package com.situ.ftbserver.mapper;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.situ.ftbserver.model.Member;
import com.situ.ftbserver.model.search.MemberSearchBean;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

@Mapper
public interface MemberMapper extends BaseMapper<Member> {
    default Page<Member> findAll(Page<Member> page, MemberSearchBean msb){

        LambdaQueryWrapper<Member> qw = Wrappers.lambdaQuery(msb);
        if(msb.getStartDate()!=null){
            qw.ge(Member::getBirthday,msb.getStartDate());
        }
        if(msb.getEndDate()!=null){
            qw.lt(Member::getBirthday,msb.getEndDate());
        }

        return selectPage(page,qw);
    }

    @Select("SELECT " +
            "    CASE " +
            "        WHEN age < 18 THEN '18岁以下'" +
            "        WHEN age BETWEEN 18 AND 25 THEN '18-25岁'" +
            "        WHEN age BETWEEN 26 AND 35 THEN '26-35岁'" +
            "        WHEN age BETWEEN 36 AND 45 THEN '36-45岁'" +
            "        WHEN age BETWEEN 46 AND 55 THEN '46-55岁'" +
            "        WHEN age > 55 THEN '55岁以上'" +
            "        ELSE '未知'" +
            "    END AS age_group," +
            "    COUNT(*) AS count " +
            "FROM member " +
            "WHERE age IS NOT NULL " +
            "GROUP BY " +
            "    CASE " +
            "        WHEN age < 18 THEN '18岁以下'" +
            "        WHEN age BETWEEN 18 AND 25 THEN '18-25岁'" +
            "        WHEN age BETWEEN 26 AND 35 THEN '26-35岁'" +
            "        WHEN age BETWEEN 36 AND 45 THEN '36-45岁'" +
            "        WHEN age BETWEEN 46 AND 55 THEN '46-55岁'" +
            "        WHEN age > 55 THEN '55岁以上'" +
            "        ELSE '未知' " +
            "    END " +
            "ORDER BY MIN(age);")
    List<Map<String, Integer>> getMemberAgeCount();
}
